pacman::p_load(tidyverse, data.table, broom)
rm(list=ls())
################################################################################

#################################### Brazil

#Currency conversion
df = fread("../../data/prices/raw/CCUSMA02BRM618N.csv")[, year:= as.integer(format(as.Date(date, format="%Y/%m/%d"),"%Y"))]
df_cu = df[, .(real_usd_rate=mean(real_usd_rate)), by = .(year)]

#Geographic units
for(unit in c('county_id','amc_id', 'microregion_id', 'mesoregion_id', 'state_id', 'region')){
  
  df_un = fread("../../data/landuse/clean/geographicunits_brazil.csv.gz")[, list(county_id, amc_id, microregion_id, mesoregion_id, state_id, region, country)]
  df_un$spatial_unit = unit
  if (unit=='county_id'){
    df_un$spatial_id = df_un$county_id 
    unit_rm_list = c('county_id')}
  if (unit=='amc_id'){
    df_un$spatial_id = df_un$amc_id 
    unit_rm_list = c('county_id','amc_id')}
  if (unit=='microregion_id'){
    df_un$spatial_id = df_un$microregion_id 
    unit_rm_list = c('county_id','amc_id','microregion_id')}
  if (unit=='mesoregion_id'){
    df_un$spatial_id = df_un$mesoregion_id 
    unit_rm_list = c('county_id','amc_id','microregion_id','mesoregion_id')}
  if (unit=='state_id'){
    df_un$spatial_id = df_un$state_id 
    unit_rm_list = c('county_id','amc_id','microregion_id','mesoregion_id','state_id')}
  if (unit=='region'){
    df_un$spatial_id = df_un$region 
    unit_rm_list = c('county_id','amc_id','microregion_id','mesoregion_id','state_id','region')}

  #Cattle transaction values
  df = fread("../../data/production/clean/cattlevalues_brazil_county_census.csv.gz")
  df$value = as.numeric(df$value)
  df = merge(df_un[,list(county_id, spatial_id)], df[value>0,], by=c('county_id'))[,-c('county_id')]
  df = df %>% rename('establishment_type'='group')
  df_ca = df[, lapply(.SD, sum, na.rm=TRUE), by = .(year, spatial_id, variable, establishment_type)]
  
  #Define variables of interest for each year
  for(data_year in c(1995,2006,2017)){
    
    df = reshape(df_ca[year==data_year,], idvar = c("year", "spatial_id","establishment_type"), timevar = "variable", direction = "wide")

    if(data_year==1995){
      df = df %>% rename('sales_q'='value.sales (head)','sales_r'='value.sales (r)',
                         'purchases_q'='value.purchases (head)' , 'purchases_r'= 'value.purchases (r)',
                         'slaughter_q'='value.slaughter (head)','slaughter_r'='value.slaughter (r)')
      df = merge(df, df_cu, by=c('year'))
      varlist_q  = c('sales_q','purchases_q','slaughter_q')
      varlist_qr = c('sales_purchases_q','purchases_slaughter_q','sales_slaughter_q')
      df[, (varlist_qr) :=list( sales_q/purchases_q, purchases_q/slaughter_q, sales_q/slaughter_q) ]
      varlist_p  = c('sales_p','purchases_p','slaughter_p')
      varlist_pr = c('sales_purchases_p','purchases_slaughter_p','sales_slaughter_p')
      df[, (varlist_p)  :=list( (sales_r/real_usd_rate)/sales_q, (purchases_r/real_usd_rate)/purchases_q, (slaughter_r/real_usd_rate)/slaughter_q) ]
      df[, (varlist_pr) :=list( sales_p/purchases_p, purchases_p/slaughter_p, sales_p/slaughter_p) ]
      df[,c('sales_r','purchases_r','slaughter_r', 'real_usd_rate'):= NULL]
      df_1995_wide = df
      varlist_melt = append(append(varlist_q, varlist_qr),append(varlist_p, varlist_pr))
      df_1995_long = melt(df_1995_wide, idvar = c("year","establishment_type"), measure.vars = varlist_melt, value.name = "value", variable.name = "value_type")
      df_1995_long = df_1995_long[value>0,]
      }
    
    if(data_year==2006){
      df =  df %>% rename('purchases_q'='value.purchases (head)', 'purchases_r'= 'value.purchases (r)',
                          'purchases1_q'='value.purchases stage 1 (head)','purchases1_r'='value.purchases stage 1 (r)',
                          'purchases2_q'='value.purchases stage 2 (head)','purchases2_r'='value.purchases stage 2 (r)',
                          'slaughter_q'='value.slaughter (head)','slaughter_r'='value.slaughter (r)',
                          'sales_q'='value.sales 782 (head)','sales_r'='value.sales 782 (r)',
                          'purchases782_q'='value.purchases 782 (head)','purchases782_r'='value.purchases 782 (r)',
                          'slaughter782_q'='value.slaughter 782 (head)','slaughter782_r'='value.slaughter 782 (r)')
      df = merge(df, df_cu, by=c('year'))
      varlist_q  = c('sales_q','purchases_q','purchases1_q','purchases2_q','slaughter_q','purchases782_q','slaughter782_q')
      varlist_qr = c('sales_purchases_q','purchases_slaughter_q','sales_slaughter_q','purchases1_purchases2_q','purchases1_slaughter_q','purchases2_slaughter_q', 'purchases1_sales_q','purchases2_sales_q','purchases_purchases782_q','slaughter_slaughter782_q')
      df[, (varlist_qr) :=list(sales_q/purchases_q, purchases_q/slaughter_q, sales_q/slaughter_q, purchases1_q/purchases2_q, purchases1_q/slaughter_q, purchases2_q/slaughter_q, purchases1_q/sales_q, purchases2_q/sales_q, purchases_q/purchases782_q, slaughter_q/slaughter782_q)]
      varlist_p  = c('sales_p', 'purchases_p','purchases1_p','purchases2_p','slaughter_p')
      varlist_pr = c('sales_purchases_p','purchases_slaughter_p','sales_slaughter_p','purchases1_purchases2_p','purchases1_slaughter_p','purchases2_slaughter_p','purchases1_sales_p','purchases2_sales_p')
      df[, (varlist_p)  :=list( (sales_r/real_usd_rate)/sales_q, (purchases_r/real_usd_rate)/purchases_q, (purchases1_r/real_usd_rate)/purchases1_q, (purchases2_r/real_usd_rate)/purchases2_q, (slaughter_r/real_usd_rate)/slaughter_q)]
      df[, (varlist_pr) :=list(sales_p/purchases_p, purchases_p/slaughter_p, sales_p/slaughter_p, purchases1_p/purchases2_p, purchases1_p/slaughter_p, purchases2_p/slaughter_p, purchases1_p/sales_p, purchases2_p/sales_p)]
      df[,c('purchases_r','purchases1_r','purchases2_r','slaughter_r','sales_r','purchases782_r','slaughter782_r','real_usd_rate'):= NULL] 
      df_2006_wide = df
      varlist_melt = append(append(varlist_q, varlist_qr),append(varlist_p, varlist_pr))
      df_2006_long = melt(df_2006_wide, idvar = c("year","establishment_type"), measure.vars = varlist_melt, value.name = "value", variable.name = "value_type")
      df_2006_long = df_2006_long[value>0,]
      }
    
    if(data_year==2017){
      df = df %>% rename("sales_q"="value.sales <50 (head)", "sales_r"="value.sales <50 (r)", 
                         "breeders_q"="value.sales breeders >50 (head)","breeders_r" ="value.sales breeders >50 (r)", 
                         "calves_q"="value.sales calves >50 (head)", "calves_r"="value.sales calves >50 (r)", 
                         "slaughter_q"="value.sales slaughter >50 (head)", "slaughter_r"="value.sales slaughter >50 (r)",
                         "all_cattle_q"="value.all cattle (head)", "breedingstock_q"="value.breeding stock (head)" )
      df = merge(df, df_cu, by=c('year'))
      varlist_q  = c('sales_q','breeders_q','calves_q','slaughter_q')
      varlist_qr = c('sales_breeders_q','sales_calves_q','sales_slaughter_q','breeders_calves_q','breeders_slaughter_q','calves_slaughter_q')
      df[, (varlist_qr) :=list( sales_q/breeders_q, sales_q/calves_q, sales_q/slaughter_q, breeders_q/calves_q, breeders_q/slaughter_q, calves_q/slaughter_q) ]
      varlist_p  = c('sales_p','breeders_p','calves_p','slaughter_p')
      varlist_pr = c('sales_breeders_p','sales_calves_p','sales_slaughter_p','breeders_calves_p','breeders_slaughter_p','calves_slaughter_p')
      df[, (varlist_p)  :=list( (sales_r/real_usd_rate)/sales_q, (breeders_r/real_usd_rate)/breeders_q, (calves_r/real_usd_rate)/calves_q, (slaughter_r/real_usd_rate)/slaughter_q  )]
      df[, (varlist_pr) :=list( sales_p/breeders_p, sales_p/calves_p, sales_p/slaughter_p, breeders_p/calves_p, breeders_p/slaughter_p, calves_p/slaughter_p  ) ]
      df[,c('sales_r','breeders_r','calves_r', 'slaughter_r','all_cattle_q','breedingstock_q','real_usd_rate'):= NULL] 
      df_2017_wide = df
      varlist_melt = append(append(varlist_q, varlist_qr),append(varlist_p, varlist_pr))
      df_2017_long = melt(df_2017_wide, idvar = c("year","establishment_type"), measure.vars = varlist_melt, value.name = "value", variable.name = "value_type")
      df_2017_long = df_2017_long[value>0,]
      }
    
  }
  
  df_all_long = rbind(df_1995_long,rbind(df_2006_long,df_2017_long))
  
  #Save final data
  df_1 = df_un[!duplicated(df_un$spatial_id), ][,(unit_rm_list):= NULL]
  df_2 = df_all_long[value_type %in% c('sales_p','purchases_p','slaughter_p',
                                       'purchases1_p','purchases2_p',
                                       'breeders_p','calves_p','slaughter_p') ,]
  df = merge(df_1, df_2, by=c('spatial_id'))[order(value_type, year, spatial_id),]
  write.csv(df, gzfile(paste0("../../data/prices/clean/farmgate_cattle_brazil_",unit,".csv.gz")), row.names = FALSE)
  
}
  
